﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Linq;
using DevExpress.XtraReports.UI;
using System.Collections;
using System.IO;
using Excel;
using System.Data.Odbc;
using System.Data.OleDb;
namespace Exact_Extension
{
    public partial class DebtorRegisterForm : DevExpress.XtraEditors.XtraForm
    {
        ISISDataContext IS = new ISISDataContext();
        IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
        DateTime EnrollmentDate;
        ExactDataContext DS;
        Common common;
        ArrayList APPDisIndex = new ArrayList();
        ArrayList TFDisIndex = new ArrayList();
        ODBCIR odbcir = new ODBCIR();
        public DebtorRegisterForm(ExactDataContext _DS)
        {
            InitializeComponent();
            EnrollmentDate = Convert.ToDateTime("01/08/" + DateTime.Now.Year.ToString(), culture);
            DS = _DS;
            
            common = new Common(DS);
            this.LoadDataInitialize();
            this.LoadListBox();
          

       
        }
        private void LoadListBox()
        {

         
            this.txtCOENumber.Text = "0";
            if (chkCOEAllocated.Checked)
            {
                this.txtCOENumber.Enabled = true;
                this.chkNewCOE.Checked = !chkCOEAllocated.Checked;
            }
            else
            {
                this.txtCOENumber.Enabled = false;

            }
            if (chkNewCOE.Checked)
            {
                this.chkCOEAllocated.Checked = !chkNewCOE.Checked;
                this.txtCOENumber.Text = "0";
            }

            var queryItem = from Item in DS.ISHCMC_V_Items
                            select Item;
            this.iSHCMCVItemBindingSource.DataSource = queryItem;
        }
        private string GetStudentIDExact()
        {
            var queryMaxDebnr = (from cc in DS.cicmpies
                                 where cc.cmp_code.Trim().StartsWith("1")
                                 select cc.cmp_code).Max();
            var queryList = from cc in DS.cicmpies
                                 where cc.cmp_code.Trim().StartsWith("1")
                                 select cc.cmp_code;
            List<int> ListCode = new List<int>();
            foreach(string item in queryList)
            {
                ListCode.Add(Convert.ToInt32(item));
            }
            var result = Enumerable.Range(100001, Convert.ToInt32(queryMaxDebnr)).Except(ListCode);
            return result.First().ToString();
        }
        private string GetInvoiceDebtor()
        {
            var queryMaxDebnr = (from cc in DS.cicmpies
                                 where cc.cmp_code.Trim().StartsWith("3")
                                 select cc.cmp_code).Max();
            var queryList = from cc in DS.cicmpies
                            where cc.cmp_code.Trim().StartsWith("3")
                            select cc.cmp_code;
            List<int> ListCode = new List<int>();
            foreach (string item in queryList)
            {
                ListCode.Add(Convert.ToInt32(item));
            }
            var result = Enumerable.Range(300001, Convert.ToInt32(queryMaxDebnr)).Except(ListCode);
            return result.First().ToString();
        }
        private void ReloadExactDebtorCode()
        {
            var queryMaxDebnr = (from cc in DS.cicmpies
                                 where cc.cmp_code.Trim().StartsWith("1")
                                 select cc.cmp_code).Max();
            this.txtStudentIDExact.Text = (Convert.ToInt32(queryMaxDebnr.Trim()) + 1).ToString();
            string StudentExactID = this.GetStudentIDExact();
            if (StudentExactID.Length > 0)
            {
                this.txtStudentIDExact.Text = StudentExactID;
            }
            var queryMaxDebnrInv = (from cc in DS.cicmpies
                                    where cc.cmp_code.Trim().StartsWith("3")
                                    select cc.cmp_code).Max();

            this.txtInvoiceDebtor.Text = (Convert.ToInt32(queryMaxDebnrInv.Trim()) + 1).ToString();
            string StudentInvoiceDebtor = this.GetInvoiceDebtor();
            if (StudentInvoiceDebtor.Length > 0)
            {
                this.txtInvoiceDebtor.Text = StudentInvoiceDebtor;
            }
        }

        public List<ISHCMC_DebtorRegister> GetListStudent()
        {
            OdbcConnection conn = new OdbcConnection("DSN=ISIT-INRE;Uid=admin;Pwd=r3dGr@ss;");
            conn.Open();

           
            List<ISHCMC_DebtorRegister> ListStudent = new List<ISHCMC_DebtorRegister>();

            string sqlComm = " Select   idInquiry,NameFirst+' ',NameFirst_ovr+' ',NameMiddle+' ' "
                + ",NameLast+' ',P1a_First+' ',P1a_Last+' ' , P1a_phone_cell+' ',P1a_email+' ',P1b_First+' ',P1b_Last+' '  "
                + " , P1b_phone_cell+ ' ',P1b_email+' ' , P1_phone_H +' ' , P1_Street +' ' , P1_City +' ' , P1_Country +' ',PWSPaymentMethod +' ' "
                + " , Billing_Name + ' ',Adm_Season + ' ',Campus + ' ',Grade_Current+' ',Enr_date,DOB, Sex+ ' ',Adm_enr_status + ' ' from INQUIRIES  ";

            


            OdbcCommand comm = new OdbcCommand();
            comm.Connection = conn;
            comm.CommandText = sqlComm;

            OdbcDataReader reader = comm.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {

                ISHCMC_DebtorRegister item = new ISHCMC_DebtorRegister();

                item.StudentIDIS = reader.GetString(0);

                item.StudentFirstName = reader.GetString(1);
                item.StudentFirstName += reader.GetString(2);

                item.StudentLastName = reader.GetString(3);
                item.StudentLastName += reader.GetString(4);
               
                item.FatherName =  reader.GetString(5);
                item.FatherName += reader.GetString(6);
                item.FatherCellPhone = reader.GetString(7);
                item.FatherEmail = reader.GetString(8);

                item.MotherName = reader.GetString(9);
                item.MotherName += reader.GetString(10);

                item.MotherCellPhone = reader.GetString(11);
                item.MotherEmail = reader.GetString(12);

                item.Homephone =  reader.GetString(13);
                item.Homeaddress1 =  reader.GetString(14);
                item.Homeaddress2 =  reader.GetString(15);
                item.Homeaddress3 =  reader.GetString(16);

                item.PaymentPlan =  reader.GetString(17);
                if (item.PaymentPlan.Length > 0)
                {
                    item.PaymentPlan = item.PaymentPlan.Substring(0, 1);
                }
                else
                {
                    item.PaymentPlan = "A";
                }
                item.BillingName =  reader.GetString(18);

                item.BillingAddress1 = reader.GetString(14);
                item.BillingAddress2 = reader.GetString(15);
                item.BillingAddress3 = reader.GetString(16);

                item.ContactName1 = item.FatherName;
                item.ContactName2 = item.MotherName;


               item.Contactaddress1 = reader.GetString(14);
                item.Contactaddress2 = reader.GetString(15);
                item.Contactaddress3 = reader.GetString(16);

                item.SchoolYear =  reader.GetString(19);
                item.School = reader.GetString(20);
                if (item.School == "A")
                {
                    item.School = "AAVN";
                }
                else if (item.School == "I")
                {
                    item.School = "ISHCMC";
                }
                item.Grade =  reader.GetString(21);
                if (item.Grade.StartsWith("E"))
                {
                    item.Grade = item.Grade.Substring(1, 2);
                }
                if (item.Grade.Length == 1)
                {
                    item.Grade = "0" + item.Grade;
                }
                //item.ActualEntryDate = Convert.ToDateTime(reader.GetString(22));
               // item.DOB = reader.GetDate(23);
                //item.Gender =  reader.GetString(24);
               // item.StudentStatus = reader.GetString(25);
                ListStudent.Add(item);

            }
            reader.Close();

            conn.Close();
            return ListStudent;

        }

        private void LoadDataInitialize()
        {
         

           
            this.iSHCMCDebtorRegisterBindingSource.DataSource = this.GetListStudent();

            
             
            var queryPayment = from PaymentMethod in DS.ISHCMC_PaymentMethods
                               select PaymentMethod;
            this.iSHCMCPaymentMethodBindingSource.DataSource = queryPayment;
            this.lookUpEdit_PaymentMethod.EditValue = queryPayment.First().Code;

            this.ReloadExactDebtorCode();
                
                
             


        }
        private void PopulateToFill(ISHCMC_ISISStudentList itemISIS)
        {
            this.LoadListBox();
            this.txtLastName.Text = itemISIS.Student___Last_Name;
            this.txtFirstName.Text = itemISIS.Student___First_Name;
            this.dateEdit_DOB.EditValue = itemISIS.Student___Birth_Date;
            if (itemISIS.Student___Gender.HasValue)
            {
                this.txtGender.Text = itemISIS.Student___Gender.Value.ToString();
            }
            if (itemISIS.Student___Last_Grade != null)
            {
                if (itemISIS.Student___Last_Grade.StartsWith("AA"))
                {
                    this.txtSchool.Text = "AAVN";
                    this.txtGradeSchool.Text = itemISIS.Student___Last_Grade.Replace("AAG", "");
                }
                else
                {
                    if (itemISIS.Student___Last_Grade.StartsWith("G"))
                    {
                        this.txtSchool.Text = "ISHCMC";
                        this.txtGradeSchool.Text = itemISIS.Student___Last_Grade.Replace("G", "");
                    }
                    else if (itemISIS.Student___Last_Grade.StartsWith("E"))
                    {
                        this.txtSchool.Text = "ISHCMC";
                        this.txtGradeSchool.Text = "E" + itemISIS.Student___Last_Grade.Replace("EE", "");
                    }
                    else if (itemISIS.Student___Last_Grade.StartsWith("K"))
                    {
                        this.txtSchool.Text = "ISHCMC";
                        this.txtGradeSchool.Text = itemISIS.Student___Last_Grade;
                    }
                }
            }
 
            this.listBoxControl_SiblingName.Items.Clear();
            if(itemISIS.Student___Actual_Entry_Date.HasValue)
            this.dateEdit_Entrydate.EditValue = itemISIS.Student___Actual_Entry_Date.Value;
            var querySibling = from Sibling in IS.ADM_WaitlistSiblings
                               where Sibling.MemberID == itemISIS.Student___MemberID
                               && Sibling.Sibling_Status.Equals("Applicant - Approved")
                               select Sibling;
            foreach (ADM_WaitlistSibling itemSibling in querySibling)
            {
                this.listBoxControl_SiblingName.Items.Add(itemSibling.SiblingMemberID.Value.ToString()
                 + " - " + itemSibling.NameLF);

            }
            if (itemISIS.Guardian_1___Relationship != null)
            {
                if (itemISIS.Guardian_1___Relationship.Equals("Father"))
                {
                    this.txtFather.Text =
                       itemISIS.Guardian_1___Title + " " + itemISIS.Guardian_1___Last_Name + " " + itemISIS.Guardian_1___Middle_Name
                            + " " + itemISIS.Guardian_1___First_Name;
                    this.txtFatherCellPhone.Text = itemISIS.Guardian_1___Cell_Phone;
                    this.txtFatherEmail.Text = itemISIS.Guardian_1___Email_1;

                    this.txtMother.Text =
                      itemISIS.Guardian_2___Title + " " + itemISIS.Guardian_2___Last_Name + " " + itemISIS.Guardian_2___Middle_Name
                           + " " + itemISIS.Guardian_2___First_Name;
                    this.txtMotherCellPhone.Text = itemISIS.Guardian_2___Cell_Phone;
                    this.txtMotherEmail.Text = itemISIS.Guardian_2___Email_2;
                }
                else
                {
                    this.txtFather.Text =
                      itemISIS.Guardian_2___Title + " " + itemISIS.Guardian_2___Last_Name + " " + itemISIS.Guardian_2___Middle_Name
                           + " " + itemISIS.Guardian_2___First_Name;
                    this.txtFatherCellPhone.Text = itemISIS.Guardian_2___Cell_Phone;
                    this.txtFatherEmail.Text = itemISIS.Guardian_2___Email_2;

                    this.txtMother.Text =
                      itemISIS.Guardian_1___Title + " " + itemISIS.Guardian_1___Last_Name + " " + itemISIS.Guardian_1___Middle_Name
                           + " " + itemISIS.Guardian_1___First_Name;
                    this.txtMotherCellPhone.Text = itemISIS.Guardian_1___Cell_Phone;
                    this.txtMotherEmail.Text = itemISIS.Guardian_1___Email_1;
                }
            }
           
            this.txtHomephone.Text = itemISIS.Student___Home_Phone;
            this.txtHomeAddress1.Text = itemISIS.Family___Address_1;
            this.txtHomeAddress2.Text = itemISIS.Family___Address_2;
            if (itemISIS.Family___Company_Name != null)
            {
                if (itemISIS.Family___Company_Name.Length > 1)
                {
                    this.txtBillingName.Text = itemISIS.Family___Company_Name;
                    this.cbxPayerType.SelectedItem = "CO";
                }
            }
            else
            {
                this.txtBillingName.Text = itemISIS.Family___Formal_Salutation;
                this.cbxPayerType.SelectedItem = "FA";
            }
            this.txtBillingAddress1.Text = "";
            this.txtBillingAddress2.Text = "";
            this.txtBillingAddress3.Text = "";
            this.txtContactAddress1.Text = "";
            this.txtContactAddress2.Text = "";
            this.txtContactAddress3.Text = "";
            this.txtTaxInvoiceAddress1.Text = "";
            this.txtTaxInvoiceAddress2.Text = "";
            this.txtTaxInvoiceAddress3.Text = "";
            if (itemISIS.Family___Billing_Address != null)
            {
                if (itemISIS.Family___Billing_Address.Length > 1)
                {

                    if (itemISIS.Family___Billing_Address.Length < 80)
                    {
                        this.txtBillingAddress1.Text = itemISIS.Family___Billing_Address;
                        this.txtContactAddress1.Text = itemISIS.Family___Billing_Address;
                    }
                    else
                    {
                        string[] tokenAddress = itemISIS.Family___Billing_Address.Split(new char[1] { ',' });
                       

                        foreach (string itemAddress in tokenAddress)
                        {
                            if (this.txtBillingAddress1.Text.Length < 80)
                            {
                                
                                this.txtBillingAddress1.Text = this.txtBillingAddress1.Text + " , " + itemAddress;
                                this.txtContactAddress1.Text = this.txtContactAddress1.Text + " , " + itemAddress;
                                this.txtTaxInvoiceAddress1.Text = this.txtTaxInvoiceAddress1.Text + " , " + itemAddress;
 
                            }
                            else
                            {

                                if (this.txtBillingAddress2.Text.Length < 80)
                                {
                                    this.txtBillingAddress2.Text = this.txtBillingAddress2.Text + " , " + itemAddress;
                                    this.txtContactAddress2.Text = this.txtContactAddress2.Text + " , " + itemAddress;
                                    this.txtTaxInvoiceAddress2.Text = this.txtTaxInvoiceAddress2.Text + " , " + itemAddress;
                                }
                                else
                                {
                                    this.txtBillingAddress3.Text = this.txtBillingAddress3.Text + " , " + itemAddress;
                                    this.txtContactAddress3.Text = this.txtContactAddress3.Text + " , " + itemAddress;
                                    this.txtTaxInvoiceAddress3.Text = this.txtTaxInvoiceAddress3.Text + " , " + itemAddress;
                                }

                            }
                        }
                    }

                }
            }
            else
            {
                this.txtBillingAddress1.Text = itemISIS.Family___Address_1;
                this.txtBillingAddress2.Text = itemISIS.Family___Address_2;

                this.txtContactAddress1.Text = itemISIS.Family___Address_1;
                this.txtContactAddress2.Text = itemISIS.Family___Address_2;

                this.txtTaxInvoiceAddress1.Text = itemISIS.Family___Address_1;
                this.txtTaxInvoiceAddress2.Text = itemISIS.Family___Address_2;
            }

            this.txtContactName1.Text =
                  itemISIS.Guardian_1___Title + " " + itemISIS.Guardian_1___Last_Name + " " + itemISIS.Guardian_1___Middle_Name
                       + " " + itemISIS.Guardian_1___First_Name;


            this.txtContactName2.Text =
              itemISIS.Guardian_2___Title + " " + itemISIS.Guardian_2___Last_Name + " " + itemISIS.Guardian_2___Middle_Name
                   + " " + itemISIS.Guardian_2___First_Name;

            this.txtContactPhone.Text = itemISIS.Guardian_1___Cell_Phone;
            this.txtContactEmail.Text = itemISIS.Guardian_1___Email_1;
            this.txtStudentIDIS.Text = lookUpEdit_StudentListIS.EditValue.ToString();

            var querySchoolY = from SCHY in IS.LU_SchoolYears
                               where SCHY.SchoolYearAbbrev == itemISIS.Student___Last_School_Year
                               select SCHY;
            if (querySchoolY.Count() > 0)
            {
                this.txtSchoolYear.Text = querySchoolY.First().SchoolYear;
            }
           

            var queryInvoiceDebtor = from InvoiceDebtor in DS.cicmpies
                                        where InvoiceDebtor.debnr.StartsWith("3")
                                     select InvoiceDebtor;
            this.cicmpyBindingSource.DataSource = queryInvoiceDebtor;
            if (queryInvoiceDebtor.Count() > 0)
            {
                this.lookUpEdit_InvoiceDebtor.EditValue = queryInvoiceDebtor.First().debnr;
            }
            var queryInvoicePayment = from InvoicePaid in DS.ISHCMC_InvoicePayments
                                      where InvoicePaid.StudentID == itemISIS.Student___MemberID.ToString()

                                      select InvoicePaid;
            this.iSHCMCInvoicePaymentBindingSource.DataSource = queryInvoicePayment;
        }
        private void PopulateToFill(ISHCMC_DebtorRegister itemDebtor)
        {
            this.LoadListBox();
            this.txtLastName.Text = itemDebtor.StudentLastName;
            this.txtFirstName.Text = itemDebtor.StudentFirstName;
            this.dateEdit_DOB.EditValue = itemDebtor.DOB;
            this.txtGender.Text = itemDebtor.Gender;
            this.txtSchool.Text = itemDebtor.School;
            this.txtGradeSchool.Text = itemDebtor.Grade;
            this.dateEdit_Entrydate.EditValue = itemDebtor.ActualEntryDate.Value;
            
            string[] tokensSiblingAAIS = itemDebtor.SiblingInAAVNISHCMC.Split(new char[1] { ';' });
            foreach (string itemSiblingAAIS in tokensSiblingAAIS)
            {
                this.listBoxControl_SiblingName.Items.Add(itemSiblingAAIS);
            }
            string[] tokensSiblinISSP = itemDebtor.SiblingInISSP.Split(new char[1] { ';' });
            if (tokensSiblinISSP.Length ==1)
            {
                this.txtSiblingISSPName1.Text = tokensSiblinISSP[0];
            }
            if (tokensSiblinISSP.Length == 2)
            {
                this.txtSiblingISSPName1.Text = tokensSiblinISSP[1];
            }
            if (tokensSiblinISSP.Length == 3)
            {
                this.txtSiblingISSPName1.Text = tokensSiblinISSP[2];
            }
       


            this.txtFather.Text = itemDebtor.FatherName;
            this.txtFatherCellPhone.Text = itemDebtor.FatherCellPhone;
            this.txtFatherEmail.Text = itemDebtor.FatherEmail;

            this.txtMother.Text = itemDebtor.MotherName;
            this.txtMotherCellPhone.Text = itemDebtor.MotherCellPhone;
            this.txtMotherEmail.Text = itemDebtor.MotherEmail;

            this.txtHomephone.Text = itemDebtor.Homephone;
            this.txtHomeAddress1.Text = itemDebtor.Homeaddress1;
            this.txtHomeAddress2.Text = itemDebtor.Homeaddress2;
            this.txtHomeAddress3.Text = itemDebtor.Homeaddress3;

            this.lookUpEdit_PaymentMethod.EditValue = itemDebtor.PaymentPlan;
            this.txtBillingName.Text = itemDebtor.BillingName;
            this.txtBillingAddress1.Text = itemDebtor.BillingAddress1;
            this.txtBillingAddress2.Text = itemDebtor.BillingAddress2;
            this.txtBillingAddress3.Text = itemDebtor.BillingAddress3;

            this.txtVATCode.Text = itemDebtor.VATCode;
            this.txtSpecialArrangment.Text = itemDebtor.SpecialArrangement;

            this.txtContactName1.Text = itemDebtor.ContactName1;
            this.txtContactName2.Text = itemDebtor.ContactName2;
            this.txtContactPhone.Text = itemDebtor.Contactphone;
            this.txtContactEmail.Text = itemDebtor.Contactemail;

            this.txtContactAddress1.Text = itemDebtor.Contactaddress1;
            this.txtContactAddress2.Text = itemDebtor.Contactaddress2;
            this.txtContactAddress3.Text = itemDebtor.Contactaddress3;

            this.txtStudentIDIS.Text = itemDebtor.StudentIDIS;
            this.txtStudentIDExact.Text = itemDebtor.StudentIDExact;
            this.txtInvoiceDebtor.Text = itemDebtor.InvoiceDebtor;

            this.cbxPayerType.SelectedItem = itemDebtor.PayerType;

            this.txtSchool.Text = itemDebtor.School;
            this.txtSchoolYear.Text = itemDebtor.SchoolYear;
            this.txtGradeSchool.Text = itemDebtor.Grade;
            this.txtAttention.Text = itemDebtor.Attention;
              

                this.txtTaxInvoiceAddress1.Text = itemDebtor.TaxInvoiceAddress1;
                this.txtTaxInvoiceAddress2.Text = itemDebtor.TaxInvoiceAddress2;
                this.txtTaxInvoiceAddress3.Text = itemDebtor.TaxInvoiceAddress3;
                this.txtRedInvoiceDisplayName.Text = itemDebtor.TaxInvoiceDisplayName;
                
                if(itemDebtor.COEAllocated.HasValue)
                this.chkCOEAllocated.Checked = itemDebtor.COEAllocated.Value;
                if(itemDebtor.COENew.HasValue)
                this.chkNewCOE.Checked = itemDebtor.COENew.Value;
                if (itemDebtor.COENumber.HasValue)
                {
                    this.txtCOENumber.Text = itemDebtor.COENumber.Value.ToString();
                }
                else
                {
                    this.txtCOENumber.Text = "";
                }
                if (itemDebtor.Halfday.HasValue)
                    this.chkHalfday.Checked = itemDebtor.Halfday.Value;
                if (itemDebtor.ADF.Value)
                    this.chkADF.Checked = itemDebtor.ADF.Value;
                var queryInvoiceDebtor = from InvoiceDebtor in DS.cicmpies
                                         where InvoiceDebtor.debnr.StartsWith("3")
                                         select InvoiceDebtor;

                this.cicmpyBindingSource.DataSource = queryInvoiceDebtor;
                if (queryInvoiceDebtor.Count() > 0)
                {
                    foreach (cicmpy cc in queryInvoiceDebtor)
                    {
                        if (cc.debnr.Trim() == itemDebtor.InvoiceDebtor)
                        {
                            this.lookUpEdit_InvoiceDebtor.EditValue = cc.debnr;
                        }
                    }
                   
                }

                var queryInvoicePayment = from InvoicePaid in DS.ISHCMC_InvoicePayments
                                          where InvoicePaid.StudentID == itemDebtor.StudentIDIS
                                          select InvoicePaid;
                this.iSHCMCInvoicePaymentBindingSource.DataSource = queryInvoicePayment;
            
            
        }

        //private void PopulateToFill(ISHCMC_DebtorRegister itemDebtor)
        //{
        //    this.LoadListBox();
        //    this.txtLastName.Text = itemDebtor.StudentLastName;
        //    this.txtFirstName.Text = itemDebtor.StudentFirstName;
        //    this.dateEdit_DOB.EditValue = itemDebtor.DOB;
        //    this.txtGender.Text = itemDebtor.Gender;
        //    this.txtSchool.Text = itemDebtor.School;
        //    this.txtGradeSchool.Text = itemDebtor.Grade;
        //    this.dateEdit_Entrydate.EditValue = itemDebtor.ActualEntryDate;
        //    if (itemDebtor.SiblingInAAVNISHCMC != null)
        //    {
        //        string[] tokensSiblingAAIS = itemDebtor.SiblingInAAVNISHCMC.Split(new char[1] { ';' });
        //        foreach (string itemSiblingAAIS in tokensSiblingAAIS)
        //        {
        //            this.listBoxControl_SiblingName.Items.Add(itemSiblingAAIS);
        //        }
        //    }
        //    if (itemDebtor.SiblingInISSP != null)
        //    {
        //        string[] tokensSiblinISSP = itemDebtor.SiblingInISSP.Split(new char[1] { ';' });
        //        if (tokensSiblinISSP.Length == 1)
        //        {
        //            this.txtSiblingISSPName1.Text = tokensSiblinISSP[0];
        //        }
        //        if (tokensSiblinISSP.Length == 2)
        //        {
        //            this.txtSiblingISSPName1.Text = tokensSiblinISSP[1];
        //        }
        //        if (tokensSiblinISSP.Length == 3)
        //        {
        //            this.txtSiblingISSPName1.Text = tokensSiblinISSP[2];
        //        }
        //    }


        //    this.txtFather.Text = itemDebtor.FatherName;
        //    this.txtFatherCellPhone.Text = itemDebtor.FatherCellPhone;
        //    this.txtFatherEmail.Text = itemDebtor.FatherEmail;

        //    this.txtMother.Text = itemDebtor.MotherName;
        //    this.txtMotherCellPhone.Text = itemDebtor.MotherCellPhone;
        //    this.txtMotherEmail.Text = itemDebtor.MotherEmail;

        //    this.txtHomephone.Text = itemDebtor.Homephone;
        //    this.txtHomeAddress1.Text = itemDebtor.Homeaddress1;
        //    this.txtHomeAddress2.Text = itemDebtor.Homeaddress2;
        //    this.txtHomeAddress3.Text = itemDebtor.Homeaddress3;

        //    this.lookUpEdit_PaymentMethod.EditValue = itemDebtor.PaymentPlan;
        //    this.txtBillingName.Text = itemDebtor.BillingName;
        //    this.txtBillingAddress1.Text = itemDebtor.BillingAddress1;
        //    this.txtBillingAddress2.Text = itemDebtor.BillingAddress2;
        //    this.txtBillingAddress3.Text = itemDebtor.BillingAddress3;

        //    this.txtVATCode.Text = itemDebtor.VATCode;
        //    this.txtSpecialArrangment.Text = itemDebtor.SpecialArrangement;

        //    this.txtContactName1.Text = itemDebtor.ContactName1;
        //    this.txtContactName2.Text = itemDebtor.ContactName2;
        //    this.txtContactPhone.Text = itemDebtor.Contactphone;
        //    this.txtContactEmail.Text = itemDebtor.Contactemail;

        //    this.txtContactAddress1.Text = itemDebtor.Contactaddress1;
        //    this.txtContactAddress2.Text = itemDebtor.Contactaddress2;
        //    this.txtContactAddress3.Text = itemDebtor.Contactaddress3;

        //    this.txtStudentIDIS.Text = itemDebtor.StudentIDIS;
        //    this.txtStudentIDExact.Text = itemDebtor.StudentIDExact;
        //    this.txtInvoiceDebtor.Text = itemDebtor.InvoiceDebtor;

        //    this.cbxPayerType.SelectedItem = itemDebtor.PayerType;

        //    this.txtSchool.Text = itemDebtor.School;
        //    this.txtSchoolYear.Text = itemDebtor.SchoolYear;
        //    this.txtGradeSchool.Text = itemDebtor.Grade;
        //    this.txtAttention.Text = itemDebtor.Attention;


        //    this.txtTaxInvoiceAddress1.Text = itemDebtor.TaxInvoiceAddress1;
        //    this.txtTaxInvoiceAddress2.Text = itemDebtor.TaxInvoiceAddress2;
        //    this.txtTaxInvoiceAddress3.Text = itemDebtor.TaxInvoiceAddress3;
        //    this.txtRedInvoiceDisplayName.Text = itemDebtor.TaxInvoiceDisplayName;

        //    if (itemDebtor.COEAllocated)
        //        this.chkCOEAllocated.Checked = itemDebtor.COEAllocated;
        //    if (itemDebtor.COENew)
        //        this.chkNewCOE.Checked = itemDebtor.COENew;
        //    if (itemDebtor.COENumber !=null)
        //    {
        //        this.txtCOENumber.Text = itemDebtor.COENumber.ToString();
        //    }
        //    else
        //    {
        //        this.txtCOENumber.Text = "";
        //    }
        //    if (itemDebtor.Halfday)
        //        this.chkHalfday.Checked = itemDebtor.Halfday;
        //    if (itemDebtor.ADF)
        //        this.chkADF.Checked = itemDebtor.ADF;
        //    var queryInvoiceDebtor = from InvoiceDebtor in DS.cicmpies
        //                             where InvoiceDebtor.debnr.StartsWith("3")
        //                             select InvoiceDebtor;

        //    this.cicmpyBindingSource.DataSource = queryInvoiceDebtor;
        //    if (queryInvoiceDebtor.Count() > 0)
        //    {
        //        foreach (cicmpy cc in queryInvoiceDebtor)
        //        {
        //            if (cc.debnr.Trim() == itemDebtor.InvoiceDebtor)
        //            {
        //                this.lookUpEdit_InvoiceDebtor.EditValue = cc.debnr;
        //            }
        //        }

        //    }

        //    var queryInvoicePayment = from InvoicePaid in DS.ISHCMC_InvoicePayments
        //                              where InvoicePaid.StudentID == itemDebtor.StudentIDIS
        //                              select InvoicePaid;
        //    this.iSHCMCInvoicePaymentBindingSource.DataSource = queryInvoicePayment;


        //}
        private void lookUpEdit_StudentListIS_EditValueChanged(object sender, EventArgs e)
        {

            var queryDebtorRegis = from DebtorRegis in DS.ISHCMC_DebtorRegisters
                                   where DebtorRegis.StudentIDIS == this.lookUpEdit_StudentListIS.EditValue.ToString()
                                   select DebtorRegis;
            var queryCC = from CC in DS.ISHCMC_V_StudentListWithDebtors
                          where CC.StudentID == this.lookUpEdit_StudentListIS.EditValue.ToString()
                          select CC;
            ISHCMC_DebtorRegister itemDebtorRegis = new ISHCMC_DebtorRegister();
            if (queryDebtorRegis.Count() > 0 || queryCC.Count() >0)
            {
                if (queryDebtorRegis.Count() > 0)
                {
                    itemDebtorRegis = queryDebtorRegis.First();
                }


             
                if (queryCC.Count() > 0)
                {
                    ISHCMC_V_StudentListWithDebtor Student = queryCC.First();
                    itemDebtorRegis.StudentIDIS = Student.StudentID;
                    itemDebtorRegis.StudentIDExact = Student.debnr;
                    itemDebtorRegis.ActualEntryDate = Student.EnrollmentDate;
                    itemDebtorRegis.Attention = Student.Attention;
                    itemDebtorRegis.BillingAddress1 = Student.BillingAddress1;
                    itemDebtorRegis.BillingAddress2 = Student.BillingAddress2;
                    itemDebtorRegis.BillingAddress3 = Student.BillingAddress3;
                    itemDebtorRegis.BillingName = Student.InvoiceDebtorName;

                    var queryContact = from contact in DS.ISHCMC_V_DebtorContacts
                                       where contact.cmp_wwn == Student.cmp_wwn
                                       select contact;
                    if (queryContact.Count() > 0)
                    {
                        foreach (ISHCMC_V_DebtorContact itemContact in queryContact)
                        {
                            if (itemContact.predcode.Trim() == "FAR")
                            {
                                itemDebtorRegis.FatherName = itemContact.FullName;
                                itemDebtorRegis.FatherEmail = itemContact.cnt_email;
                                itemDebtorRegis.FatherCellPhone = itemContact.cnt_f_tel;
                                itemDebtorRegis.Contactaddress1 = itemContact.Address1;
                                itemDebtorRegis.Contactaddress2 = itemContact.Address2;
                                itemDebtorRegis.Contactaddress3 = itemContact.Address3;
                            }
                            if (itemContact.predcode.Trim() == "MOT")
                            {
                                itemDebtorRegis.MotherName = itemContact.FullName;
                                itemDebtorRegis.MotherEmail = itemContact.cnt_email;
                                itemDebtorRegis.MotherCellPhone = itemContact.cnt_f_tel;
                                itemDebtorRegis.Contactaddress1 = itemContact.Address1;
                                itemDebtorRegis.Contactaddress2 = itemContact.Address2;
                                itemDebtorRegis.Contactaddress3 = itemContact.Address3;
                            }
                            if (itemContact.predcode.Trim() == "OT")
                            {
                                itemDebtorRegis.ContactName1 = itemContact.FullName;
                                itemDebtorRegis.Contactemail = itemContact.cnt_email;
                                itemDebtorRegis.Contactphone = itemContact.cnt_f_tel;
                                itemDebtorRegis.Contactaddress1 = itemContact.Address1;
                                itemDebtorRegis.Contactaddress2 = itemContact.Address2;
                                itemDebtorRegis.Contactaddress3 = itemContact.Address3;
                            }
                        }
                    }

                    itemDebtorRegis.DOB = Student.DOB;
                    itemDebtorRegis.Gender = Student.Gender;
                    itemDebtorRegis.Grade = Student.Grade;
                    itemDebtorRegis.Homeaddress1 = Student.Address1;
                    itemDebtorRegis.Homeaddress2 = Student.Address2;
                    itemDebtorRegis.Homeaddress3 = Student.Address3;
                    itemDebtorRegis.Homephone = Student.Phone;
                    itemDebtorRegis.InvoiceDebtor = Student.InvoiceDebtor;
                    if (Student.PayeeType != null)
                    {
                        itemDebtorRegis.PayerType = Student.PayeeType.Trim();
                    }
                    itemDebtorRegis.PaymentPlan = Student.PaymentMethod;
                    itemDebtorRegis.School = Student.School;
                    itemDebtorRegis.SchoolYear = Student.SchoolYear;
                    if (Student.StudentName.Length > 0)
                    {
                        itemDebtorRegis.StudentLastName = "";
                        itemDebtorRegis.StudentFirstName = "";
                        string[] tokensName = Student.StudentName.Split(new char[1] { ' ' });

                        if (tokensName.Count() > 1)
                        {
                            itemDebtorRegis.StudentLastName = tokensName[0];
                            for (int i = 1; i < tokensName.Count(); i++)
                            {

                                itemDebtorRegis.StudentFirstName = itemDebtorRegis.StudentFirstName + " " + tokensName[i];
                            }
                        }
                        else
                        {
                            itemDebtorRegis.StudentLastName = tokensName[0];
                            itemDebtorRegis.StudentFirstName = "";
                        }
                    }
                    itemDebtorRegis.StudentIDExact = Student.debnr;
                    itemDebtorRegis.StudentIDIS = Student.StudentID;
                    itemDebtorRegis.TaxInvoiceAddress1 = Student.TaxInvoiceAddress1;
                    itemDebtorRegis.TaxInvoiceAddress2 = Student.TaxInvoiceAddress2;
                    itemDebtorRegis.TaxInvoiceAddress3 = Student.TaxInvoiceAddress3;
                    itemDebtorRegis.TaxInvoiceDisplayName = Student.TaxInvoiceName;
                    itemDebtorRegis.VATCode = Student.VATCode;
                    itemDebtorRegis.COENew = Student.COENew;
                    itemDebtorRegis.COEAllocated = Student.COEAllocated;
                    itemDebtorRegis.COENumber = Convert.ToInt32(Student.COENumber.ToString());
                    itemDebtorRegis.Halfday = Student.HalfdayCheck;
                    itemDebtorRegis.ADF = Student.ADF;
                    var querySibling = from Sibling in DS.ISHCMC_V_StudentListWithDebtors
                                       where Sibling.InvoiceDebtor == Student.InvoiceDebtor
                                       select Sibling;
                    itemDebtorRegis.SiblingInISSP = "";
                    itemDebtorRegis.SiblingInAAVNISHCMC = "";
                    if (querySibling.Count() > 0)
                    {
                        foreach (ISHCMC_V_StudentListWithDebtor itemSibling in querySibling)
                        {
                            if (itemSibling.StudentID != itemDebtorRegis.StudentIDIS)
                            {
                                itemDebtorRegis.SiblingInAAVNISHCMC = itemSibling.StudentID + "-" + itemSibling.StudentName +";";
                            }
                        }

                    }
                    else
                    {
                        itemDebtorRegis.SiblingInAAVNISHCMC = "";
                    }
                }




                this.PopulateToFill(itemDebtorRegis);
                this.chk_CreateNewDebtor.Enabled = false;
            }

            else
            {

                //var querySTDetail = from STDetail in IS.ISHCMC_ISISStudentLists
                //                    where STDetail.Student___MemberID == Convert.ToInt32(this.lookUpEdit_StudentListIS.EditValue)
                //                    select STDetail;
                //this.listBoxControl_SiblingName.Items.Clear();
                //if (querySTDetail.Count() > 0)
                //{
                //    ISHCMC_ISISStudentList itemISIS = querySTDetail.First();
                   
                //    this.PopulateToFill(itemISIS);
                //    this.ReloadExactDebtorCode();
                //    this.chk_CreateNewDebtor.Enabled = true;
                //    this.chk_CreateNewDebtor.Checked = true;
                //}
            
                ISHCMC_DebtorRegister itemDebtor = odbcir.GetStudentInfo(this.lookUpEdit_StudentListIS.EditValue.ToString());
       
                this.PopulateToFill(itemDebtor);
                this.ReloadExactDebtorCode();
                this.chk_CreateNewDebtor.Enabled = true;
                this.chk_CreateNewDebtor.Checked = true;
                
            }
        }
        private void btnSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {

            this.SaveData();

        }
        private void SaveData()
        {
            List<ISHCMC_DebtorRegister> ListDebtor = new List<ISHCMC_DebtorRegister>();
            string SiblingAAIS = "";
            foreach (object itemSibling in this.listBoxControl_SiblingName.Items)
            {
                SiblingAAIS += itemSibling.ToString() + " ; ";
            }
            string invoicedebtor = "";



            if (this.chk_CreateNewDebtor.Checked)
            {
                invoicedebtor = this.txtInvoiceDebtor.Text;
            }
            else
            {
                if (lookUpEdit_InvoiceDebtor.EditValue == null)
                {
                    MessageBox.Show("Please choose invoice debtor or create new invoice debtor");
                }
                else
                {
                    invoicedebtor = this.lookUpEdit_InvoiceDebtor.EditValue.ToString();
                }
            }
            if (!common.CheckDebtorRegisterExist(this.txtStudentIDIS.Text))
            {

                ISHCMC_DebtorRegister Debtor = new ISHCMC_DebtorRegister
                {
                    //Basic information
                    StudentIDIS = this.lookUpEdit_StudentListIS.EditValue.ToString(),
                    StudentLastName = this.txtLastName.Text,
                    StudentFirstName = this.txtFirstName.Text,
                    DOB = Convert.ToDateTime(this.dateEdit_DOB.EditValue),
                    Gender = this.txtGender.Text,
                    ActualEntryDate = Convert.ToDateTime(this.dateEdit_Entrydate.EditValue),
                    SiblingInAAVNISHCMC = SiblingAAIS,
                    SiblingInISSP = this.txtSiblingISSPName1.Text + " ; " + this.txtSiblingISSPName2.Text + " ; " + this.txtSiblingISSPName3.Text,
                    FatherName = this.txtFather.Text,
                    FatherCellPhone = this.txtFatherCellPhone.Text,
                    FatherEmail = this.txtFatherEmail.Text,
                    MotherName = this.txtMother.Text,
                    MotherCellPhone = this.txtMotherCellPhone.Text,
                    MotherEmail = this.txtMotherEmail.Text,
                    Homephone = this.txtHomephone.Text,
                    Homeaddress1 = this.txtHomeAddress1.Text,
                    Homeaddress2 = this.txtHomeAddress2.Text,
                    Homeaddress3 = this.txtHomeAddress3.Text,
                    //Billing information
                    PaymentPlan = this.lookUpEdit_PaymentMethod.EditValue.ToString(),
                    BillingName = this.txtBillingName.Text,
                    BillingAddress1 = this.txtBillingAddress1.Text,
                    BillingAddress2 = this.txtBillingAddress2.Text,
                    BillingAddress3 = this.txtBillingAddress3.Text,
                    VATCode = this.txtVATCode.Text,
                    SpecialArrangement = this.txtSpecialArrangment.Text,

                    ContactName1 = this.txtContactName1.Text,
                    ContactName2 = this.txtContactName2.Text,
                    Contactphone = this.txtContactPhone.Text,
                    Contactemail = this.txtContactEmail.Text,
                    Contactaddress1 = this.txtContactAddress1.Text,
                    Contactaddress2 = this.txtContactAddress2.Text,
                    Contactaddress3 = this.txtContactAddress3.Text,

                    StudentIDExact = this.txtStudentIDExact.Text,
                    PayerType = this.cbxPayerType.Text,
                    InvoiceDebtor = invoicedebtor,

                    School = this.txtSchool.Text,
                    Grade = this.txtGradeSchool.Text,
                    SchoolYear = this.txtSchoolYear.Text,
                    Attention = this.txtAttention.Text,
                    TaxInvoiceAddress1 = this.txtTaxInvoiceAddress1.Text,
                    TaxInvoiceAddress2 = this.txtTaxInvoiceAddress2.Text,
                    TaxInvoiceAddress3 = this.txtTaxInvoiceAddress3.Text,
                    TaxInvoiceDisplayName = this.txtRedInvoiceDisplayName.Text,
                    COEAllocated = this.chkCOEAllocated.Checked,
                    COENew = this.chkNewCOE.Checked,
                    COENumber = Convert.ToInt32(this.txtCOENumber.Text),
                    Halfday = this.chkHalfday.Checked,
                    ADF = this.chkADF.Checked


                };
                DS.ISHCMC_DebtorRegisters.InsertOnSubmit(Debtor);

                if (this.gridView_Payment.RowCount > 0)
                {
                    for (int i = 0; i < this.gridView_Payment.RowCount; i++)
                    {
                        ISHCMC_InvoicePayment itemPaid = (ISHCMC_InvoicePayment)this.gridView_Payment.GetRow(i);
                        if (itemPaid.ID == null || itemPaid.ID == 0)
                        {
                            ISHCMC_InvoicePayment itemPaidInsert = new ISHCMC_InvoicePayment()
                            {
                                StudentID = Debtor.StudentIDIS,
                                PaidAmount = itemPaid.PaidAmount,
                                PaymentDate = itemPaid.PaymentDate,
                                PaymentType = "Cash",
                                ItemCode = itemPaid.ItemCode,
                                ReferenceNo = itemPaid.ReferenceNo,
                                Syscreated = DateTime.Now,
                                Sysmodified = DateTime.Now,
                                Syscreator = Environment.UserName,
                                Sysmodifier = Environment.UserName
                            };
                            DS.ISHCMC_InvoicePayments.InsertOnSubmit(itemPaidInsert);
                        }
                        else
                        {
                            ISHCMC_InvoicePayment itemPaidUpdate = DS.ISHCMC_InvoicePayments.Single(c => c.ID == itemPaid.ID);
                            itemPaidUpdate.PaidAmount = itemPaid.PaidAmount;
                            itemPaidUpdate.PaymentDate = itemPaid.PaymentDate;
                            itemPaidUpdate.ReferenceNo = itemPaid.ReferenceNo;
                            itemPaidUpdate.Sysmodified = DateTime.Now;
                            itemPaidUpdate.Sysmodifier = Environment.UserName;
                        }

                    }
                }

                XMLExport xml = new XMLExport(DS);
                if (this.chk_CreateNewDebtor.Checked)
                {
                    string filename1 = xml.ExportXMLInvoiceDebtor(Debtor);
                    xml.StartProcessImportDebtor(filename1);
                }

                string filename2 = xml.ExportXMLStudent(Debtor);
                xml.StartProcessImportDebtor(filename2);
                ListDebtor.Add(Debtor);

                DebtorRegisterRpt rpt = new DebtorRegisterRpt(ListDebtor, DS);
                ReportPrintTool PrintTool = new ReportPrintTool(rpt);
                PrintTool.ShowPreview();
                DS.SubmitChanges();
                this.PopulateToFill(Debtor);
            }
            else
            {

                ISHCMC_DebtorRegister Debtor = DS.ISHCMC_DebtorRegisters.Single(c => c.StudentIDIS == this.txtStudentIDIS.Text);
                //Basic information
                Debtor.StudentIDIS = this.lookUpEdit_StudentListIS.EditValue.ToString();
                Debtor.StudentLastName = this.txtLastName.Text;
                Debtor.StudentFirstName = this.txtFirstName.Text;
                Debtor.DOB = Convert.ToDateTime(this.dateEdit_DOB.EditValue);
                Debtor.Gender = this.txtGender.Text;
                Debtor.ActualEntryDate = Convert.ToDateTime(this.dateEdit_Entrydate.EditValue);
                Debtor.SiblingInAAVNISHCMC = SiblingAAIS;
                Debtor.SiblingInISSP = this.txtSiblingISSPName1.Text + " ; " + this.txtSiblingISSPName2.Text + " ; " + this.txtSiblingISSPName3.Text;
                Debtor.FatherName = this.txtFather.Text;
                Debtor.FatherCellPhone = this.txtFatherCellPhone.Text;
                Debtor.FatherEmail = this.txtFatherEmail.Text;
                Debtor.MotherName = this.txtMother.Text;
                Debtor.MotherCellPhone = this.txtMotherCellPhone.Text;
                Debtor.MotherEmail = this.txtMotherEmail.Text;
                Debtor.Homephone = this.txtHomephone.Text;
                Debtor.Homeaddress1 = this.txtHomeAddress1.Text;
                Debtor.Homeaddress2 = this.txtHomeAddress2.Text;
                Debtor.Homeaddress3 = this.txtHomeAddress3.Text;
                //Billing information
                Debtor.PaymentPlan = this.lookUpEdit_PaymentMethod.EditValue.ToString();
                Debtor.BillingName = this.txtBillingName.Text;
                Debtor.BillingAddress1 = this.txtBillingAddress1.Text;
                Debtor.BillingAddress2 = this.txtBillingAddress2.Text;
                Debtor.BillingAddress3 = this.txtBillingAddress3.Text;
                Debtor.VATCode = this.txtVATCode.Text;
                Debtor.SpecialArrangement = this.txtSpecialArrangment.Text;

                Debtor.ContactName1 = this.txtContactName1.Text;
                Debtor.ContactName2 = this.txtContactName2.Text;
                Debtor.Contactphone = this.txtContactPhone.Text;
                Debtor.Contactemail = this.txtContactEmail.Text;
                Debtor.Contactaddress1 = this.txtContactAddress1.Text;
                Debtor.Contactaddress2 = this.txtContactAddress2.Text;
                Debtor.Contactaddress3 = this.txtContactAddress3.Text;

                Debtor.StudentIDExact = this.txtStudentIDExact.Text;
                Debtor.PayerType = this.cbxPayerType.Text;
                Debtor.InvoiceDebtor = invoicedebtor;

                Debtor.School = this.txtSchool.Text;
                Debtor.Grade = this.txtGradeSchool.Text;
                Debtor.SchoolYear = this.txtSchoolYear.Text;
                Debtor.Attention = this.txtAttention.Text;
                Debtor.TaxInvoiceAddress1 = this.txtTaxInvoiceAddress1.Text;
                Debtor.TaxInvoiceAddress2 = this.txtTaxInvoiceAddress2.Text;
                Debtor.TaxInvoiceAddress3 = this.txtTaxInvoiceAddress3.Text;
                Debtor.TaxInvoiceDisplayName = this.txtRedInvoiceDisplayName.Text;
                Debtor.COEAllocated = this.chkCOEAllocated.Checked;
                Debtor.COENew = this.chkNewCOE.Checked;
                Debtor.COENumber = Convert.ToInt32(this.txtCOENumber.Text);
                Debtor.Halfday = this.chkHalfday.Checked;
                Debtor.ADF = this.chkADF.Checked;

                if (this.gridView_Payment.RowCount > 0)
                {
                    for (int i = 0; i < this.gridView_Payment.RowCount; i++)
                    {
                        ISHCMC_InvoicePayment itemPaid = (ISHCMC_InvoicePayment)this.gridView_Payment.GetRow(i);
                        if (itemPaid.ID == 0)
                        {
                            ISHCMC_InvoicePayment itemPaidInsert = new ISHCMC_InvoicePayment()
                            {
                                StudentID = Debtor.StudentIDIS,
                                PaidAmount = itemPaid.PaidAmount,
                                PaymentDate = itemPaid.PaymentDate,
                                PaymentType = "Cash",
                                ItemCode = itemPaid.ItemCode,
                                ReferenceNo = itemPaid.ReferenceNo,
                                Syscreated = DateTime.Now,
                                Sysmodified = DateTime.Now,
                                Syscreator = Environment.UserName,
                                Sysmodifier = Environment.UserName
                            };
                            DS.ISHCMC_InvoicePayments.InsertOnSubmit(itemPaidInsert);
                        }
                        else
                        {
                            ISHCMC_InvoicePayment itemPaidUpdate = DS.ISHCMC_InvoicePayments.Single(c => c.ID == itemPaid.ID);
                            itemPaidUpdate.PaidAmount = itemPaid.PaidAmount;
                            itemPaidUpdate.PaymentDate = itemPaid.PaymentDate;
                            itemPaidUpdate.ReferenceNo = itemPaid.ReferenceNo;
                            itemPaidUpdate.Sysmodified = DateTime.Now;
                            itemPaidUpdate.Sysmodifier = Environment.UserName;
                        }

                    }
                }
                XMLExport xml = new XMLExport(DS);
                if (this.chk_CreateNewDebtor.Checked & this.chk_CreateNewDebtor.Enabled)
                {
                    string filename1 = xml.ExportXMLInvoiceDebtor(Debtor);
                    xml.StartProcessImportDebtor(filename1);
                }

                string filename2 = xml.ExportXMLStudent(Debtor);
                xml.StartProcessImportDebtor(filename2);
                ListDebtor.Add(Debtor);

                DebtorRegisterRpt rpt = new DebtorRegisterRpt(ListDebtor, DS);
                ReportPrintTool PrintTool = new ReportPrintTool(rpt);
                PrintTool.ShowPreview();
                DS.SubmitChanges();
                this.PopulateToFill(Debtor);
            }
        
        }
        private void gridView_Student_RowClick(object sender, DevExpress.XtraGrid.Views.Grid.RowClickEventArgs e)
        {
            if (e.Button == System.Windows.Forms.MouseButtons.Left)
            {
                if (e.RowHandle >= 0)
                {
                    ISHCMC_DebtorRegister itemDebtor = (ISHCMC_DebtorRegister)this.gridView_Student.GetRow(e.RowHandle);
                    this.lookUpEdit_StudentListIS.EditValue = itemDebtor.StudentIDIS;
                }
            }
        }
        private void btnPrint_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (this.gridView_Student.GetSelectedRows().Count() > 0)
            {
                FamilyMember itemISIS = (FamilyMember)this.gridView_Student.GetRow(this.gridView_Student.GetSelectedRows()[0]);

                List<ISHCMC_DebtorRegister> ListDebtor = new List<ISHCMC_DebtorRegister>();
                var queryDebtor = from Debtor in DS.ISHCMC_DebtorRegisters
                                  where Debtor.StudentIDIS == itemISIS.MemberID.ToString()
                                  select Debtor;
                foreach (ISHCMC_DebtorRegister itemDebtor in queryDebtor)
                {
                    itemDebtor.PayerType = common.GetClassification(itemDebtor.InvoiceDebtor.ToString());
                    ListDebtor.Add(itemDebtor);
                }
                DebtorRegisterRpt rpt = new DebtorRegisterRpt(ListDebtor,DS);
                ReportPrintTool PrintTool = new ReportPrintTool(rpt);
                PrintTool.ShowPreview();
            }

        }
        private void chkCOEAllocated_CheckedChanged(object sender, EventArgs e)
        {
            if (chkCOEAllocated.Checked)
            {
                this.txtCOENumber.Enabled = true;
                this.chkNewCOE.Checked = !chkCOEAllocated.Checked;
                this.chkADF.Checked = !chkCOEAllocated.Checked;
            }
            else
            {
                this.txtCOENumber.Enabled = false;
               
            }
        }
        private void chkNewCOE_CheckedChanged(object sender, EventArgs e)
        {
            if (chkNewCOE.Checked)
            {
                this.chkCOEAllocated.Checked = !chkNewCOE.Checked;
                this.txtCOENumber.Text = "0";
                this.chkADF.Checked = !chkNewCOE.Checked;
            }
         
        }
        private void chkADF_CheckedChanged(object sender, EventArgs e)
        {
            if (chkADF.Checked)
            {
                this.chkCOEAllocated.Checked = !chkADF.Checked;
                this.txtCOENumber.Text = "0";
                this.chkNewCOE.Checked = !chkADF.Checked;
            }
        }
        private void btnBrowse_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DialogResult resultDialog = this.openFileDialog1.ShowDialog();
            if (resultDialog == DialogResult.OK)
            {
                this.txtFileUpload.EditValue = this.openFileDialog1.FileName;
                FileInfo fileinfo = new FileInfo(this.txtFileUpload.EditValue.ToString());
                FileStream stream = File.Open(this.txtFileUpload.EditValue.ToString(), FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = null;
                if (fileinfo.Extension == ".xls")
                {
                    //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                if (fileinfo.Extension == ".xlsx")
                {
                    //...
                    //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }

                //4. DataSet - Create column names from first row
                excelReader.IsFirstRowAsColumnNames = true;
                DataSet result = excelReader.AsDataSet();

               
                int i = 0;
                List<FamilyMember> ListFAM = new List<FamilyMember>();
                while (excelReader.Read())
                {
                    if (i > 0)
                    {
                        var queryFM = from FM in IS.FamilyMembers
                                      where FM.MemberID == Convert.ToInt32(excelReader.GetString(0))
                                      select FM;
                        if (queryFM.Count() > 0)
                        {
                            ListFAM.Add(queryFM.First());
                        }
                    }
                    i++;
                }

                //6. Free resources (IExcelDataReader is IDisposable)
                excelReader.Close();
                this.familyMemberBindingSource.DataSource = ListFAM;
                
            }
        }

      
     



    }
}